﻿/*
 * 文件名：Common.cs
 * 类名：XHCTMS.DBHELP.Common
 * 创建人：仇祖萍
 * 创建时间：2013-05-20
 * 备注；
 * 
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace XHCTMS.DBHELP
{
    public class Common
    {
        //泛型类和泛型方法同时具备可重用性、类型安全和效率

        #region 对数据库进行非查询操作 (增删改)
        /// <summary>
        /// 对数据库进行增删改操作
        /// </summary>
        /// <param name="queryString"> 操作字符串</param>
        /// <returns>返回int类型 >=1/操作成功, 0/失败, -1/异常</returns>
        public int ExcuteQuery(string queryString)
        {
            using (SqlConnection conn = new DbTools().GetConn())
            {
                int result = 0;
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = queryString;

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                cmd.Transaction = conn.BeginTransaction();
                SqlTransaction tran = cmd.Transaction;
                try
                {
                    result = cmd.ExecuteNonQuery();
                    tran.Commit();
                    return result;
                }
                catch
                {
                    tran.Rollback();
                    return result = -1;
                }

            }
        }


        /// <summary>
        /// 对数据库进行增删改操作
        /// </summary>
        /// <param name="queryString">操作字符串</param>
        /// <param name="paras">Command参数</param>
        /// <returns>返回int类型 >=1/操作成功, 0/失败, -1/异常</returns>
        public int ExcuteQuery(string queryString, SqlParameter[] paras)
        {
            using (SqlConnection conn = new DbTools().GetConn())
            {
                int result = 0;
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = queryString;
                cmd.Parameters.AddRange(paras);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                cmd.Transaction = conn.BeginTransaction();
                SqlTransaction tran = cmd.Transaction;
                try
                {
                    result = cmd.ExecuteNonQuery();
                    tran.Commit();
                    return result;
                }
                catch
                {
                    tran.Rollback();
                    return result = -1;
                }

            }
        }

        /// <summary>
        /// 对数据库进行增删改操作
        /// </summary>
        /// <param name="queryString">操作字符串</param>
        /// <param name="paras">Command参数</param>
        /// <returns>返回int类型 >=1/操作成功, 0/失败, -1/异常</returns>
        public int ExcuteProcQuery(string queryString, SqlParameter[] paras)
        {
            using (SqlConnection conn = new DbTools().GetConn())
            {
                int result = 0;
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = queryString;
                cmd.Parameters.AddRange(paras);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                cmd.Transaction = conn.BeginTransaction();
                SqlTransaction tran = cmd.Transaction;
                try
                {
                    result = cmd.ExecuteNonQuery();
                    tran.Commit();
                    return result;
                }
                catch
                {
                    tran.Rollback();
                    return result = -1;
                }

            }
        }

        /// <summary>
        /// 对数据库进行增删改操作
        /// </summary>
        /// <param name="queryString">操作字符串</param>
        /// <param name="paras">Command参数</param>
        /// <returns>返回int类型 >=1/操作成功, 0/失败, -1/异常</returns>
        public int ExcuteQuery(string queryString, List<SqlParameter> paras)
        {
            using (SqlConnection conn = new DbTools().GetConn())
            {
                int result = 0;
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = queryString;

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                foreach (SqlParameter para in paras)
                {
                    cmd.Parameters.Add(para);
                }

                cmd.Transaction = conn.BeginTransaction();
                SqlTransaction tran = cmd.Transaction;
                try
                {
                    result = cmd.ExecuteNonQuery();
                    tran.Commit();
                    return result;
                }
                catch
                {
                    tran.Rollback();
                    return result = -1;
                }

            }
        }
        #endregion

        #region 对数据库进行查询操作 返回DataTable类型
        /// <summary>
        /// 对数据库进行查询操作
        /// </summary>
        /// <param name="queryString">查询字符串</param>
        /// <returns>返回DataTable类型 null/异常</returns>
        public DataTable GetDataTable(string queryString)
        {
            using (SqlConnection conn = new DbTools().GetConn())
            {
                DataTable dt = new DataTable();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = queryString;
                try
                {
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    da.Fill(dt);
                    return dt;
                }
                catch
                {
                    return dt = null;
                }

            }
        }

        /// <summary>
        /// 对数据库进行查询操作
        /// </summary>
        /// <param name="queryString">查询字符串</param>
        /// <param name="paras">Command参数</param>
        /// <returns>返回DataTable类型 null/异常</returns>
        public DataTable GetDataTable(string queryString, SqlParameter[] paras)
        {
            using (SqlConnection conn = new DbTools().GetConn())
            {
                DataTable dt = new DataTable();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = queryString;
                
                cmd.Parameters.AddRange(paras);
                try
                {
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    da.Fill(dt);
                    return dt;
                }
                catch
                {
                    return dt = null;
                }

            }
        }

        public DataTable GetDataTableProc(string queryString, SqlParameter[] paras)
        {
            using (SqlConnection conn = new DbTools().GetConn())
            {
                DataTable dt = new DataTable();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = queryString;
                cmd.Parameters.AddRange(paras);
                try
                {
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    da.Fill(dt);
                    return dt;
                }
                catch
                {
                    return dt = null;
                }

            }
        }

        /// <summary>
        /// 对数据库进行查询操作
        /// </summary>
        /// <param name="queryString">查询字符串</param>
        /// <param name="paras">Command参数</param>
        /// <returns>返回DataTable类型 null/异常</returns>
        public DataTable GetDataTable(string queryString, List<SqlParameter> paras)
        {
            using (SqlConnection conn = new DbTools().GetConn())
            {
                DataTable dt = new DataTable();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = queryString;

                foreach (SqlParameter para in paras)
                {
                    cmd.Parameters.Add(para);
                }
                try
                {
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    da.Fill(dt);
                    return dt;
                }
                catch
                {
                    return dt = null;
                }

            }
        }
        #endregion

        #region 对数据库进行查询操作 返回DataSet

        /// <summary>
        /// 对数据库进行查询操作
        /// </summary>
        /// <param name="queryString">查询字符串</param>
        /// <returns>返回DataSet类型 null/异常</returns>
        public DataSet GetDataSet(string queryString)
        {
            using (SqlConnection conn = new DbTools().GetConn())
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = queryString;
                try
                {
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    da.Fill(ds);
                    return ds;
                }
                catch
                {
                    return ds = null;
                }

            }
        }

        /// <summary>
        /// 对数据库进行查询操作
        /// </summary>
        /// <param name="queryString">查询字符串</param>
        /// <param name="paras">Command参数</param>
        /// <returns>返回DataSet类型 null/异常</returns>
        public DataSet GetDataSet(string queryString, SqlParameter[] paras)
        {
            using (SqlConnection conn = new DbTools().GetConn())
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = queryString;
                cmd.Parameters.AddRange(paras);
                try
                {
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    da.Fill(ds);
                    return ds;
                }
                catch
                {
                    return ds = null;
                }

            }
        }

        /// <summary>
        /// 对数据库进行查询操作
        /// </summary>
        /// <param name="queryString">查询字符串</param>
        /// <param name="paras">Command参数</param>
        /// <returns>返回DataSet类型 null/异常</returns>
        public DataSet GetDataSet(string queryString, List<SqlParameter> paras)
        {
            using (SqlConnection conn = new DbTools().GetConn())
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = queryString;

                foreach (SqlParameter para in paras)
                {
                    cmd.Parameters.Add(para);
                }
                try
                {
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    da.Fill(ds);
                    return ds;
                }
                catch
                {
                    return ds = null;
                }

            }
        }

        #endregion

        #region 对数据库进行查询操作， 并返回第一行第一列的值

        /// <summary>
        /// 对数据库进行查询操作,并返回第一行第一列的值
        /// </summary>
        /// <param name="queryString">查询字符串</param>
        /// <returns>返回string类型 null/异常</returns>
        public string GetDataScalar(string queryString)
        {
            using (SqlConnection conn = new DbTools().GetConn())
            {
                string Str = null;
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = queryString;
                try
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    Str = cmd.ExecuteScalar().ToString();
                    return Str;
                }
                catch
                {
                    return Str;
                }

            }
        }

        /// <summary>
        /// 对数据库进行查询操作,并返回第一行第一列的值
        /// </summary>
        /// <param name="queryString">查询字符串</param>
        /// <param name="paras">Command参数</param>
        /// <returns>返回string类型 null/异常</returns>
        public string GetDataScalar(string queryString, SqlParameter[] paras)
        {
            using (SqlConnection conn = new DbTools().GetConn())
            {
                string Str = null;
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = queryString;
                cmd.Parameters.AddRange(paras);
                try
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    Str = cmd.ExecuteScalar().ToString();
                    return Str;
                }
                catch
                {
                    return Str;
                }

            }
        }

        /// <summary>
        /// 对数据库进行查询操作,并返回第一行第一列的值
        /// </summary>
        /// <param name="queryString">查询字符串</param>
        /// <param name="paras">Command参数</param>
        /// <returns>返回string类型 null/异常</returns>
        public string GetDataScalar(string queryString, List<SqlParameter> paras)
        {
            using (SqlConnection conn = new DbTools().GetConn())
            {
                String Str = null;
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = queryString;
                foreach (SqlParameter para in paras)
                {
                    cmd.Parameters.Add(para);
                }

                try
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    Str = cmd.ExecuteScalar().ToString();
                    return Str;
                }
                catch
                {
                    return Str;
                }

            }
        }

        #endregion
    }
}
